PostgreSQL 窗口函数
1 本章背景知识
PostgreSQL 数据库定义了许多专门用于数据分析的窗口函数（Window Function）。
窗口函数是针对每一行数据，基于和它相关的一组数据计算出一个结果。下图演示了聚合函数和窗口函数的效果：
2 环境准备
导入示例库PostgreSQL HR 示例库。
导入示例库 [[sales_montyly.sql]]。
导入示例库 [[sales_data.sql]]。
3 聚合函数和窗口函数
下示例分别将 AVG、COUNT、SUM 作为聚合函数和窗口函数，计算员工的平均月薪、人数总和以及月薪总和。
3.1 聚合函数
SELECT AVG(salary), COUNT(*), SUM(salary)
FROM employees;
//屏幕输出：
avg |count| sum |
---------------------|-----|---------|
6461.8317757009345794| 107|691416.00|
3.2 窗口函数
SELECT employee_id,first_name, last_name, AVG(salary) OVER (), COUNT(*) OVER (), SUM(salary) OVER ()
FROM employees;
//屏幕输出：
employee_id|first_name |last_name |avg |count|sum |
-----------|-----------|-----------|---------------------|-----|---------|
100|Steven |King |6461.8317757009345794| 107|691416.00|
101|Neena |Kochhar |6461.8317757009345794| 107|691416.00|
102|Lex |De Haan |6461.8317757009345794| 107|691416.00|
... ...
聚合函数通常也可以作为窗口函数，区别在于后者包含了 OVER 关键字；空括号表示将所有数据作为整体进行分析，所以得到的数值和聚合函数一样。第二个区别，窗口函数为每一个员工都返回了一个结果。
4 窗口函数的分类
常见的窗口函数可以分为以下几类
1、聚合窗口函数。
2、排名窗函数。
3、取值窗口函数。
PostgreSQL 还提供了更多复杂的窗口选项，可以参考官方文档。
5 窗口函数的定义
窗口函数的定义如下：
window_function ( expression, ... ) OVER (
PARTITION BY ...
ORDER BY ...
frame_clause
)
1、window_function 是窗口函数的名称；
2、expression 是参数，有些函数不需要参数；
3、 OVER 子句包含三个选项：
（1）分区（PARTITION BY）。
（2）排序（ORDER BY）。
（3）窗口大小（frame_clause）。
下面将会对这三个选项进行详细阐述。
5.1 分区选项（PARTITION BY）
PARTITION BY 选项用于定义分区，作用类似于 GROUP BY 的分组。
1、如果指定了分区选项，窗口函数将会分别针对每个分区单独进行分析；
2、如果省略分区选项，所有的数据作为一个整体进行分析。
以下语句按照部门进行分组，分析每个部门的平均月薪：
SELECT first_name, last_name, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id)
FROM employees
ORDER BY department_id;
//屏幕输出：
first_name | last_name | department_id | salary | AVG
-------------+-------------+---------------+----------+-----
| Jennifer | Whalen | 10 | 4400.00 |4400.0000000000000000
Pat | Fay | 20 | 6000.00 |9500.0000000000000000
Michael | Hartstein | 20 | 13000.00 |9500.0000000000000000
Shelli | Baida | 30 | 2900.00 |4150.0000000000000000
Karen | Colmenares | 30 | 2500.00 |4150.0000000000000000
Den | Raphaely | 30 | 11000.00 |4150.0000000000000000
Alexander | Khoo | 30 | 3100.00 |4150.0000000000000000
Sigal | Tobias | 30 | 2800.00 |4150.0000000000000000
Guy | Himuro | 30 | 2600.00 |4150.0000000000000000
Susan | Mavris | 40 | 6500.00 |6500.0000000000000000
Steven | Markle | 50 | 2200.00 |3475.5555555555555556
Laura | Bissot | 50 | 3300.00 |3475.5555555555555556
Mozhe | Atkinson | 50 | 2800.00 |3475.5555555555555556
James | Marlow | 50 | 2500.00 |3475.5555555555555556
... ...
部门 10 只有一个员工，平均月薪就是她自己的月薪 4400；部门 20 有两个员工，平均月薪等于 (6000 + 13000)/2 = 9500；其他数据依次类推。
5.2 排序选项（ORDER BY）
ORDER BY 选项用于指定分区内的排序方式，通常用于数据的排名分析。
以下语句用于计算每个员工在部门内的入职顺序：
SELECT first_name, last_name, department_id, hire_date,
RANK() OVER (PARTITION BY department_id ORDER BY hire_date)
FROM employees
ORDER BY department_id;
//屏幕输出：
first_name | last_name | department_id | hire_date | RANK
-------------+-------------+---------------+---------------------+------
Jennifer | Whalen | 10 | 1987-09-17 00:00:00 | 1
Michael | Hartstein | 20 | 1996-02-17 00:00:00 | 1
Pat | Fay | 20 | 1997-08-17 00:00:00 | 2
Den | Raphaely | 30 | 1994-12-07 00:00:00 | 1
Alexander | Khoo | 30 | 1995-05-18 00:00:00 | 2
Sigal | Tobias | 30 | 1997-07-24 00:00:00 | 3
Shelli | Baida | 30 | 1997-12-24 00:00:00 | 4
Guy | Himuro | 30 | 1998-11-15 00:00:00 | 5
Karen | Colmenares | 30 | 1999-08-10 00:00:00 | 6
... ...
PARTITION BY选项表示按照部门进行分区；ORDER BY选项指定在部门内按照入职先后进行排序；- RANK 函数用于计算名次，在 排名窗口函数 将会进行介绍。
- 部门 10 只有一个员工，Jennifer 就是第一个入职的员工；
- 部门 20 有两个员工，Michael（1996-02-17）比 Pat（1997-08-17）入职更早；
ORDER BY 子句同样支持 NULLS FIRST 和 NULLS LAST 选项，用于指定空值的排序顺序。默认为 NULLS LAST。
5.3 窗口选项（frame_clause）
frame_clause 选项用于在当前分区内指定一个计算窗口。
指定了窗口之后，分析函数不再基于分区进行计算，而是基于窗口内的数据进行计算。
5.3.1 窗口选项语法
{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end
1、 ROWS 表示在分区中计算窗口的偏移量。
2、 RANGE 表示以数值（例如 30 分钟）为单位计算窗口的偏移量。
3、其中，frame_start 用于定义窗口的起始位置，可以指定以下内容之一。
（1）UNBOUNDED PRECEDING，窗口从第一行开始，此参数为默认值；
（2） N PRECEDING，窗口从当前行之前的第 N 行或者数值开始；
（3）CURRENT ROW，窗口从当前行开始。
4、frame_end 用于定义窗口的结束位置，可以指定以下内容之一：
（1）CURRENT ROW，窗口到当前行结束，默认值；
（2）N FOLLOWING，窗口到当前行之后的第 N 行或者数值结束；
（3）UNBOUNDED FOLLOWING，窗口到分区的最后一行结束。
下图可以方便我们理解这些选项的含义：
5.3.2 窗口选项使用的注意事项
1、CURRENT ROW 表示当前正在处理的行；
2、其他的行可以使用相对当前行的位置表示。
3、常用的聚合函数，例如 AVG、SUM、COUNT 等，也可以作为窗口函数使用。
需要注意，窗口的大小不会超出当前分区的范围。
5.4 窗口函数的运行步骤
1、运行 GROUP BY 分组、聚合函数以及 HAVING 过滤。
2、运行多个窗口函数。
3、如果多个窗口函数拥有相同的 PARTITION BY 和 ORDER BY 选项，它们会在遍历数据时一起进行读取输入数据。
6 聚合窗口函数
6.1 计算每个产品当前月份的累计销量
SELECT product AS "产品", ym "年月", amount "销量",
SUM(amount) OVER (PARTITION BY product ORDER BY ym ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM sales_monthly
ORDER BY product, ym;
//屏幕输出：
产品 | 年月 | 销量 | SUM
------+--------+----------+-----------
桔子 | 201801 | 10154.00 | 10154.00
桔子 | 201802 | 10183.00 | 20337.00
桔子 | 201803 | 10245.00 | 30582.00
桔子 | 201804 | 10325.00 | 40907.00
桔子 | 201805 | 10465.00 | 51372.00
桔子 | 201806 | 10505.00 | 61877.00
......
1、PARTITION BY 选项表示按照产品进行分区；
2、ORDER BY 选项表示按照日期进行排序；
3、窗口子句 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指定窗口从当前分区的第一行开始到当前行结束；
4、SUM 函数计算的是产品累计到当前月份为止的销量合计。
6.2 使用 AVG 函数计算移动平均值
SELECT saledate, amount, avg(amount) OVER (ORDER BY saledate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM sales_data
WHERE product = '桔子' AND channel = '淘宝';
//屏幕输出：
saledate | amount | avg
---------------------+---------+-----------------------
2019-01-01 00:00:00 | 1864.00 | 1893.5000000000000000
2019-01-02 00:00:00 | 1923.00 | 1505.3333333333333333
2019-01-03 00:00:00 | 729.00 | 1066.3333333333333333
2019-01-04 00:00:00 | 547.00 | 966.6666666666666667
2019-01-05 00:00:00 | 1624.00 | 1272.0000000000000000
2019-01-06 00:00:00 | 1645.00 | 1332.0000000000000000
2019-01-07 00:00:00 | 727.00 | 1394.3333333333333333
2019-01-08 00:00:00 | 1811.00 | 1182.3333333333333333
2019-01-09 00:00:00 | 1009.00 | 1218.6666666666666667
2019-01-10 00:00:00 | 836.00 | 1172.0000000000000000
... ...
该语句返回了“桔子”在“淘宝”上的销量，以及每一天和它前后一天（共 3 天）的平均销量。
6.3 移动平均值通常用于处理时间序列的数据
例如，厂房的温度检测器获取了每秒钟的温度，我们可以使用以下窗口计算前五分钟内的平均温度。
AVG(temperature) OVER (ORDER BY ts RANGE BETWEEN interval '5 minute' PRECEDING AND CURRENT ROW)
7 排名窗口函数
7.1 常见的排名窗口函数
排名窗口函数用于对数据进行分组排名。常见的排名窗口函数包括：
| 函数 | 说明 |
|---|---|
| ROW_NUMBER | 为分区中的每行数据分配一个序列号，序列号从 1 开始分配。 |
| RANK | 计算每行数据在其分区中的名次；如果存在名次相同的数据，后续的排名将会产生跳跃。 |
| DENSE_RANK | 计算每行数据在其分区中的名次；即使存在名次相同的数据，后续的排名也是连续的值。 |
| PERCENT_RANK | 以百分比的形式显示每行数据在其分区中的名次；如果存在名次相同的数据，后续的排名将会产生跳跃。 |
| CUME_DIST | 计算每行数据在其分区内的累积分布，也就是该行数据及其之前的数据的比率；取值范围大于 0 并且小于等于 1。 |
| NTILE | 将分区内的数据分为 N 等份，为每行数据计算其所在的位置。 |
排名窗口函数不支持动态的窗口大小（frame_clause），而是以当前分区作为分析的窗口。
7.2 按照部门为单位，计算员工的月薪排名
SELECT d.department_name "部门名称", concat(e.first_name, ',' , e.last_name) "姓名", e.salary "月薪",
ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS "row_number",
RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS "rank",
DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS "dense_rank",
PERCENT_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS "percent_rank"
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE d.department_name in ('IT', 'Purchasing')
ORDER BY 1, 4;
//屏幕输出：
部门名称 | 姓名 | 月薪 | row_number | rank | dense_rank | percent_rank
------------+------------------+----------+------------+------+------------+--------------
IT | Alexander,Hunold | 9000.00 | 1 | 1 | 1 | 0
IT | Bruce,Ernst | 6000.00 | 2 | 2 | 2 | 0.25
IT | Valli,Pataballa | 4800.00 | 3 | 3 | 3 | 0.5
IT | David,Austin | 4800.00 | 4 | 3 | 3 | 0.5
IT | Diana,Lorentz | 4200.00 | 5 | 5 | 4 | 1
Purchasing | Den,Raphaely | 11000.00 | 1 | 1 | 1 | 0
Purchasing | Alexander,Khoo | 3100.00 | 2 | 2 | 2 | 0.2
Purchasing | Shelli,Baida | 2900.00 | 3 | 3 | 3 | 0.4
Purchasing | Sigal,Tobias | 2800.00 | 4 | 4 | 4 | 0.6
Purchasing | Guy,Himuro | 2600.00 | 5 | 5 | 5 | 0.8
Purchasing | Karen,Colmenares | 2500.00 | 6 | 6 | 6 | 1
1、ROW_NUMBER 函数为每个员工分配了一个连续的数字编号，可以看作是一种排名。IT 部门的“Valli,Pataballa”和“David,Austin”的月薪相同，但是rownumber不同；
2、RANK 函数为每个员工指定了一个名次。IT 部门的“Valli,Pataballa”和“David,Austin”的名次都是 3；而且在他们之后的“Diana,Lorentz”的名次为 5，产生了跳跃；
3、DENSE_RANK 函数为每个员工指定了一个名次，IT 部门的“Valli,Pataballa”和“David,Austin”的名次都是 3；在他们之后的“Diana,Lorentz”的名次为 4，名次是连续值；
4、PERCENT_RANK 函数按照百分比指定名次，取值位于 0 到 1 之间。其中“Diana,Lorentz”的百分比排名为 1，也产生了跳跃。
7.3 当窗口范围一样时，则可以简化写法
以上示例中 4 个窗口函数的 OVER 子句完全相同，此时可以采用一种更简单的写法：
SELECT d.department_name "部门名称", concat(e.first_name, ',' , e.last_name) "姓名", e.salary "月薪",
ROW_NUMBER() OVER w AS "row_number",
RANK() OVER w AS "rank",
DENSE_RANK() OVER w AS "dense_rank",
PERCENT_RANK() OVER w AS "percent_rank"
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE d.department_name in ('IT', 'Purchasing')
WINDOW w AS (PARTITION BY e.department_id ORDER BY e.salary DESC)
ORDER BY 1, 4;
//屏幕输出：
部门名称 | 姓名 | 月薪 | row_number | rank | dense_rank | percent_rank
------------+------------------+----------+------------+------+------------+--------------
IT | Alexander,Hunold | 9000.00 | 1 | 1 | 1 | 0
IT | Bruce,Ernst | 6000.00 | 2 | 2 | 2 | 0.25
IT | Valli,Pataballa | 4800.00 | 3 | 3 | 3 | 0.5
IT | David,Austin | 4800.00 | 4 | 3 | 3 | 0.5
IT | Diana,Lorentz | 4200.00 | 5 | 5 | 4 | 1
Purchasing | Den,Raphaely | 11000.00 | 1 | 1 | 1 | 0
Purchasing | Alexander,Khoo | 3100.00 | 2 | 2 | 2 | 0.2
Purchasing | Shelli,Baida | 2900.00 | 3 | 3 | 3 | 0.4
Purchasing | Sigal,Tobias | 2800.00 | 4 | 4 | 4 | 0.6
Purchasing | Guy,Himuro | 2600.00 | 5 | 5 | 5 | 0.8
Purchasing | Karen,Colmenares | 2500.00 | 6 | 6 | 6 | 1
其中，WINDOW 定义了一个窗口变量 w，然后在窗口函数的 OVER 子句中使用了该变量；这样可以简化函数的输入。
7.4 CUME_DIST 和 NTILE 函数
SELECT concat(first_name, ',' , last_name) "姓名", hire_date AS "入职日期",
CUME_DIST() OVER (ORDER BY hire_date) AS "累积占比",
NTILE(100) OVER (ORDER BY hire_date) AS "相对位置"
FROM employees;
//屏幕输出：
姓名 | 入职日期 | 累积占比 | 相对位置
-------------------+---------------------+----------------------+----------
Steven,King | 1987-06-17 00:00:00 | 0.009345794392523364 | 1
Jennifer,Whalen | 1987-09-17 00:00:00 | 0.018691588785046728 | 1
Neena,Kochhar | 1989-09-21 00:00:00 | 0.028037383177570093 | 2
Alexander,Hunold | 1990-01-03 00:00:00 | 0.037383177570093455 | 2
Bruce,Ernst | 1991-05-21 00:00:00 | 0.04672897196261682 | 3
Lex,De Haan | 1993-01-13 00:00:00 | 0.056074766355140186 | 3
Shelley,Higgins | 1994-06-07 00:00:00 | 0.09345794392523364 | 4
William,Gietz | 1994-06-07 00:00:00 | 0.09345794392523364 | 4
Susan,Mavris | 1994-06-07 00:00:00 | 0.09345794392523364 | 5
Hermann,Baer | 1994-06-07 00:00:00 | 0.09345794392523364 | 5
Daniel,Faviet | 1994-08-16 00:00:00 | 0.102803738317757 | 6
Nancy,Greenberg | 1994-08-17 00:00:00 | 0.11214953271028037 | 6
Den,Raphaely | 1994-12-07 00:00:00 | 0.12149532710280374 | 7
Payam,Kaufling | 1995-05-01 00:00:00 | 0.1308411214953271 | 7
Alexander,Khoo | 1995-05-18 00:00:00 | 0.14018691588785046 | 8
... ...
CUME_DIST 函数显示 1987-09-17 以及之前入职的员工大概有 1.8%（2/107）；NTILE(100) 函数表明前 2% 入职的员工有“Steven,King”和“Jennifer,Whalen”。
由于员工总数为 107，所以对于统计并不是完全准确。
8 取值窗口函数
8.1 常见的取值窗口函数
取值窗口函数用于返回指定位置上的数据。常见的取值窗口函数包括：
| 函数名 | 说明 |
|---|---|
| FIRST_VALUE | 返回窗口内第一行的数据。 |
| LAST_VALUE | 返回窗口内最后一行的数据。 |
| NTH_VALUE | 返回窗口内第 N 行的数据。 |
| LAG | 返回分区中当前行之前的第 N 行的数据。 |
| LEAD | 返回分区中当前行之后第 N 行的数据。 |
LAG 和 LEAD 函数不支持动态的窗口大小（frame_clause），是以当前分区作为分析的窗口。
8.2 获取每个部门内部月薪最高、月薪最低以及月薪第三高的员工
以下语句使用 FIRST_VALUE、LAST_VALUE 以及 NTH_VALUE 函数分别获取每个部门内部月薪最高、月薪最低以及月薪第三高的员工：
SELECT department_id, first_name, last_name, salary,
FIRST_VALUE(salary) OVER w,
LAST_VALUE(salary) OVER w,
NTH_VALUE(salary, 3) OVER w
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY department_id, salary DESC;
//屏幕输出：
department_id | first_name | last_name | salary | FIRST_VALUE | LAST_VALUE | NTH_VALUE
---------------+-------------+-------------+----------+-------------+------------+-----------
10 | Jennifer | Whalen | 4400.00 | 4400.00 | 4400.00 |
20 | Michael | Hartstein | 13000.00 | 13000.00 | 6000.00 |
20 | Pat | Fay | 6000.00 | 13000.00 | 6000.00 |
30 | Den | Raphaely | 11000.00 | 11000.00 | 2500.00 | 2900.00
30 | Alexander | Khoo | 3100.00 | 11000.00 | 2500.00 | 2900.00
30 | Shelli | Baida | 2900.00 | 11000.00 | 2500.00 | 2900.00
30 | Sigal | Tobias | 2800.00 | 11000.00 | 2500.00 | 2900.00
30 | Guy | Himuro | 2600.00 | 11000.00 | 2500.00 | 2900.00
30 | Karen | Colmenares | 2500.00 | 11000.00 | 2500.00 | 2900.00
... ... ...
1、以上三个函数的默认窗口是从当前分区的第一行到当前行，所以我们在 OVER 子句中将窗口设置为整个分区。
8.3 统计不同产品每个月的环比增长率
LAG 和 LEAD 函数同样用于计算销量数据的环比/同比增长。
例如，以下语句统计不同产品每个月的环比增长率：
WITH sales_monthly AS (
SELECT product, to_char(saledate,'YYYYMM') ym, sum(amount) sum_amount
FROM sales_data
GROUP BY product, to_char(saledate,'YYYYMM')
)
SELECT product AS "产品", ym "年月", sum_amount "销量",
(sum_amount - LAG(sum_amount, 1) OVER (PARTITION BY product ORDER BY ym))/
LAG(sum_amount, 1) OVER (PARTITION BY product ORDER BY ym) * 100 AS "环比增长率（%）"
FROM sales_monthly
ORDER BY product, ym;
//屏幕输出：
产品 | 年月 | 销量 | 环比增长率（%）
------+--------+-----------+-------------------------
桔子 | 201901 | 126083.00 |
桔子 | 201902 | 119417.00 | -5.28699348841636065100
桔子 | 201903 | 147290.00 | 23.34089786211343443600
桔子 | 201904 | 147848.00 | 0.37884445651435942700
桔子 | 201905 | 182417.00 | 23.38144580920945836300
桔子 | 201906 | 186206.00 | 2.07710904137224052600
苹果 | 201901 | 125083.00 |
苹果 | 201902 | 124421.00 | -0.52924857894358146200
苹果 | 201903 | 150230.00 | 20.74328288632947814300
苹果 | 201904 | 170360.00 | 13.39945417027224921800
苹果 | 201905 | 180136.00 | 5.73843625264146513300
苹果 | 201906 | 186822.00 | 3.71164009415108584600
香蕉 | 201901 | 120557.00 |
香蕉 | 201902 | 118356.00 | -1.82569241105866934300
香蕉 | 201903 | 155412.00 | 31.30893237351718544100
香蕉 | 201904 | 161857.00 | 4.14704141250353898000
香蕉 | 201905 | 181689.00 | 12.25279104394619942300
香蕉 | 201906 | 187498.00 | 3.19722162596524830900
... ... ... ...
1、创建一个通用表表达式 sales_monthly，得到了不同产品每个月的销量汇总；
2、LAG(sum_amount, 1) 表示获取上一期的销量；
3、当前月份的销量减去上个月的销量，再除以上个月的销量，就是环比增长率。